import os
import re
import glob
import datetime
import pandas as pd
from sqlalchemy import text, create_engine
import psycopg2
from psycopg2 import sql


import psycopg2
from psycopg2 import sql


def create_DB(schema_name, table_name, columns_info, db_config):
    try:
        # 创建到数据库的连接
        conn = psycopg2.connect(**db_config)
        cur = conn.cursor()

        # 如果不存在，则创建架构
        cur.execute(sql.SQL("CREATE SCHEMA IF NOT EXISTS {}").format(sql.Identifier(schema_name)))
        conn.commit()

        # 构造 CREATE TABLE 查询
        column_defs = [
            sql.SQL('{} {}').format(
                sql.Identifier(col_name),
                sql.SQL(columns_info[col_name]['type']) if 'type' in columns_info[col_name] else sql.SQL('TEXT')
            ) + (sql.SQL(' PRIMARY KEY') if columns_info[col_name].get('primary_key', False) else sql.SQL(''))
            for col_name in columns_info
        ]

        create_table_query = sql.SQL("""
            CREATE TABLE IF NOT EXISTS {}.{} (
                {}
            )
        """).format(
            sql.Identifier(schema_name),
            sql.Identifier(table_name),
            sql.SQL(', ').join(column_defs)
        )

        # 创建表
        cur.execute(create_table_query)
        conn.commit()

        # 删除表中的所有数据（如果有的话）
        delete_query = sql.SQL("DELETE FROM {}.{}").format(
            sql.Identifier(schema_name),
            sql.Identifier(table_name)
        )
        cur.execute(delete_query)
        conn.commit()

        print(f"成功在架构 {schema_name} 中创建表 {table_name}")

    except psycopg2.Error as e:
        print(f"创建表时出错: {e}")
        if conn:
            conn.rollback()  # 回滚事务以撤销当前的更改

    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()


# 创建数据表
if __name__ == '__main__':
    db_config = {
        'dbname': 'postgres',
        'user': '5ga-cmcc',
        'password': '5ga-cmcc',
        'host': 'localhost',
        'port': '5432'
    }
    schema_name = 'public'
    

    columns_info = {
        'time': {'type': 'timestamp without time zone'},
        'port_name': {'type': 'CHARACTER VARYING'},
        'rx_bytes': {'type': 'integer'},
        'tx_bytes': {'type': 'integer'},
        'id': {'type': 'text'}
    }
    table_name = 'traffic_table'

    create_DB(schema_name, table_name, columns_info, db_config)